package com.advertisementsystem.Mapper;

import com.advertisementsystem.entity.Bill;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

@Mapper
public interface BillMapper extends BaseMapper<Bill> {

    @Select("<script>" +
            "SELECT b.*, " +
            "n.ad_id, " +
            "a.title, " +
            "u1.username as advertiser_name, " +
            "u2.username as device_owner_name " +
            "FROM bill b " +
            "JOIN ad_negotiation n ON b.negotiation_id = n.id " +
            "JOIN advertisement a ON n.ad_id = a.id " +
            "JOIN user u1 ON b.advertiser_id = u1.id " +
            "JOIN user u2 ON b.device_owner_id = u2.id " +
            "<where> " +
            "<if test='status != null'> AND b.status = #{status} </if> " +
            "<if test='advertiserId != null'> AND b.advertiser_id = #{advertiserId} </if> " +
            "<if test='deviceOwnerId != null'> AND b.device_owner_id = #{deviceOwnerId} </if> " +
            "<if test='keyword != null and keyword != \"\"'> " +
            "AND (a.title LIKE CONCAT('%', #{keyword}, '%') " +
            "OR u1.username LIKE CONCAT('%', #{keyword}, '%') " +
            "OR u2.username LIKE CONCAT('%', #{keyword}, '%')) " +
            "</if> " +
            "</where> " +
            "ORDER BY b.created_time DESC" +
            "</script>")
    IPage<Bill> getBillsWithDetails(
            Page<Bill> page,
            @Param("status") String status,
            @Param("advertiserId") Long advertiserId,
            @Param("deviceOwnerId") Long deviceOwnerId,
            @Param("keyword") String keyword);

    @Select("SELECT b.*, " +
            "n.ad_id, " +
            "a.title, " +
            "u1.username as advertiser_name, " +
            "u2.username as device_owner_name " +
            "FROM bill b " +
            "JOIN ad_negotiation n ON b.negotiation_id = n.id " +
            "JOIN advertisement a ON n.ad_id = a.id " +
            "JOIN user u1 ON b.advertiser_id = u1.id " +
            "JOIN user u2 ON b.device_owner_id = u2.id " +
            "WHERE b.id = #{id}")
    Bill getBillWithDetailsById(@Param("id") Long id);


    /**
     * 获取设备拥有者某月的总收入（已支付的账单）
     * @param deviceOwnerId 设备拥有者ID
     * @param year 年份
     * @param month 月份
     * @return 总收入金额
     */
    @Select("SELECT COALESCE(SUM(amount), 0) FROM bill " +
            "WHERE device_owner_id = #{deviceOwnerId} " +
            "AND status = 'PAID' " +
            "AND YEAR(payment_date) = #{year} " +
            "AND MONTH(payment_date) = #{month}")
    BigDecimal getMonthlyIncome(@Param("deviceOwnerId") Long deviceOwnerId,
                                @Param("year") int year,
                                @Param("month") int month);


    /**
     * 获取设备拥有者的月度收入统计
     * @param deviceOwnerId 设备拥有者ID
     * @param year 年份
     * @return 月度收入统计
     */
    @Select("SELECT MONTH(payment_date) AS month, COALESCE(SUM(amount), 0) AS total " +
            "FROM bill " +
            "WHERE device_owner_id = #{deviceOwnerId} " +
            "AND status = 'PAID' " +
            "AND YEAR(payment_date) = #{year} " +
            "GROUP BY MONTH(payment_date) " +
            "ORDER BY month")
    List<Map<String, Object>> getMonthlyIncomeStats(@Param("deviceOwnerId") Long deviceOwnerId,
                                                    @Param("year") int year);

    /**
     * 获取设备拥有者的收入汇总信息
     * @param deviceOwnerId 设备拥有者ID
     * @return 收入汇总信息
     */
    @Select("SELECT " +
            "COALESCE(SUM(CASE WHEN status = 'PAID' THEN amount ELSE 0 END), 0) AS totalPaid, " +
            "COALESCE(SUM(CASE WHEN status = 'UNPAID' THEN amount ELSE 0 END), 0) AS totalUnpaid, " +
            "COALESCE(SUM(CASE WHEN status = 'OVERDUE' THEN amount ELSE 0 END), 0) AS totalOverdue, " +
            "COUNT(CASE WHEN status = 'PAID' THEN 1 ELSE NULL END) AS paidCount, " +
            "COUNT(CASE WHEN status = 'UNPAID' THEN 1 ELSE NULL END) AS unpaidCount, " +
            "COUNT(CASE WHEN status = 'OVERDUE' THEN 1 ELSE NULL END) AS overdueCount " +
            "FROM bill " +
            "WHERE device_owner_id = #{deviceOwnerId}")
    Map<String, Object> getIncomeSummary(@Param("deviceOwnerId") Long deviceOwnerId);
    /**
     * 获取最近收入记录
     * @param deviceOwnerId 设备拥有者ID
     * @param limit 限制数量
     * @return 最近收入记录
     */
    @Select("SELECT b.id, b.amount, b.payment_date, b.negotiation_id, a.username AS advertiser_name " +
            "FROM bill b " +
            "JOIN user a ON b.advertiser_id = a.id " +
            "WHERE b.device_owner_id = #{deviceOwnerId} " +
            "AND b.status = 'PAID' " +
            "ORDER BY b.payment_date DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> getRecentIncomeRecords(@Param("deviceOwnerId") Long deviceOwnerId,
                                                     @Param("limit") Integer limit);
}